codesharefandomcom-20200215-history
SQL Standards and Conventions
Formatting If you can't read your code, you're going to spend much more time than necessary poring through the mish mash of code. Anyone who has been forced to sift through unformatted SQL knows what that means! There are a number of formatting methods and preferences. Always remember one thing: KISS. Indenting Don't put yourself through the editing pain of Tabbing...then space-space--oh, dang...now it's not lined up...backspace...now that looks right. A favored method is to group clauses and indent only when the next line is subordinate a parent clause. SELECT c.id, c.fName, c.lName, c.zip FROM Clients AS c INNER JOIN Purchases AS p ON p.clientID = c.id WHERE c.lName LIKE 'Smit%' AND c.zip LIKE '841%'; Note how the SELECT clause's fields are each on their own line. This makes the ordering, inclusion and exclusions of fields much easier to handle in your editor. Other formatting techniques line up the right side of each of the SQL keywords. SELECT field1, field2, field3 FROM table1 AS t1 INNER JOIN table2 AS t2 ON t2.t2ID = t1.id The previous example is certainly readable and does not require odd insertions of spaces that fall outside the standard tab stops. Semicolons? Note the semicolon (';') at the end. While very optional, and rarely used, this should be used in large scripts to give the eye one more aid in differentiating one command from another. Double-Spacing It's a good idea to separate SQL commands from one another with double spaces. INSERT INTO Foo ( newFoo, newFoo2 ) VALUES ( 'bar', 'pub' ); SELECT * -- please, please, PLEASE NEVER use this! 'SELECT *' bad! FROM Foo; Transactions Now, of some controversy is the indenting of SQL between BEGIN TRANSACTION and COMMIT or ROLLBACK TRANSACTION. It's clear that those commands are not loop delimiters, and yet, it makes perfect sense to indent all SQL in between those commands since all commands within those TRANSACTION references are subordinate. Besides, it makes it really easy to see what transaction is doing what and when. BEGIN TRANSACTION; DELETE FROM Foo WHERE newFoo = 'wee'; UPDATE Foo SET newFoo = 'yung' WHERE newFoo2 = 'fee'; COMMIT TRANSACTION; The above is nice and all, but a little better (and every little bit can count!)... BEGIN TRANSACTION; DELETE FROM Foo WHERE newFoo = 'wee'; UPDATE Foo SET newFoo = 'yung' WHERE newFoo2 = 'fee'; COMMIT TRANSACTION; Bustin' Caps Notice another convention: the all-caps of any SQL keyword. This holds true for all keywords. This does not hold true for functions. Functions simply look better when shown in PascalCase. OBJECT_ID() while more "standard" is a function, not SQL. Object_ID() just looks so much better, doesn't it? Aliasing Another optional piece of SQL that is often left out is the 'AS' keyword. There is no reason to put it into your SQL, right? Well, not quite; using the 'AS' keyword gives still more visual cues as to what all is going on in the SQL, making it easier to read without having to re-scan what you just read. "What was this 'a' table reference again?" Summary Of course, code formatting ever has been and ever will be a thing of personal preference. And just like every book or instructor you've encountered, the best advice is to choose something that's easy to read, and stay consistent! Category:SQL